package com.iris.live.services.common;

import com.mysql.jdbc.Connection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * Created by HangLiu on 2016/12/12.
 */
public class JDBCBaseUtils {
    static Properties pros = null;
    private static Logger log = LoggerFactory.getLogger(JDBCBaseUtils.class);
    static {   //程序启动时加载,只会被加载一次
        pros = new Properties();
        try {
            //获取配置文件
            pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("application.dev.properties"));
        } catch (IOException e) {
            log.error("JDBC获取数据库文件异常", e);
        }
    }
    public static Connection getConnection() {
        String driver = pros.getProperty("db.driverClassName");
        String url = pros.getProperty("db.url");
        String username = pros.getProperty("db.username");
        String password = pros.getProperty("db.password");
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            log.error("JDBC加载驱动异常", e);
        } catch (SQLException e) {
            log.error("JDBC获取连接异常", e);
        }
        return conn;
    }
    // 运行有结果，没参数的sql语句
    public static List runSelectSql(String sql) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rst = null;
        List resultList = new ArrayList();
        try {
            con = getConnection();
            pst = con.prepareStatement(sql);
            rst = pst.executeQuery();
            ResultSetMetaData md = rst.getMetaData();
            int columnCount = md.getColumnCount();
            // 通过ResultSupport对象的toResult方法来获取result对象
            while (rst.next()) {
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rst.getObject(i));
                }
                resultList.add(rowData);
            }
        } catch (Exception e) {
            log.error("JDBC运行异常", e);
        } finally {
            try {
                rst.close();
                pst.close();
                con.close();
            } catch (Exception e) {
                log.error("Connection关闭异常", e);
            }
        }
        return resultList;
    }
    // 运行有结果，有参数的sql语句
    public static List runSelectSql(String sql, Object[] params) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rst = null;
        List resultList = new ArrayList();
        try {
            con = getConnection();
            pst = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            rst = pst.executeQuery();
            ResultSetMetaData md = rst.getMetaData();
            int columnCount = md.getColumnCount();
            // 通过ResultSupport对象的toResult方法来获取result对象
            while (rst.next()) {
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), rst.getObject(i));
                }
                resultList.add(rowData);
            }
        } catch (Exception e) {
            log.error("JDBC运行异常", e);
        } finally {
            try {
                rst.close();
                pst.close();
                con.close();
            } catch (Exception e) {
                log.error("Connection关闭异常", e);
            }
        }
        return resultList;
    }
    // 运行没有结果，有参数的sql语句(插入，修改等语句)
    public static boolean runUpdateSql(String sql, Object[] params) {
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = getConnection();
            con.setAutoCommit(false);
            pst = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            pst.executeUpdate();
            con.commit();
            return true;
        } catch (Exception e) {
            log.error("JDBC运行异常", e);
            try {
                con.rollback();
            } catch (Exception ex) {
                log.error("JDBC回滚异常", ex);
            } finally {
                return false;
            }
        } finally {
            try {
                pst.close();
                con.close();
            } catch (Exception e) {
                log.error("Connection关闭异常", e);
            }
        }
    }
    //没有结果，没有参数
    public static boolean runUpdateSql(String sql) {
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = getConnection();
            con.setAutoCommit(false);
            pst = con.prepareStatement(sql);
            pst.executeUpdate();
            con.commit();
            return true;
        } catch (Exception e) {
            log.error("JDBC运行异常", e);
            try {
                con.rollback();
            } catch (Exception ex) {
                log.error("JDBC回滚异常", ex);
            } finally {
                return false;
            }
        } finally {
            try {
                pst.close();
                con.close();
            } catch (Exception e) {
                log.error("Connection关闭异常", e);
            }
        }
    }
}
